package com.monas.client.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.monas.client.util.Node;
import com.monas.client.view.CPUView;
import com.monas.client.view.HarddiskView;
import com.monas.client.view.MemoryView;

public class MonasResourceDatabase {

	private static final String USER = "monas_user";

	private static final String PASSWORD = "monas_pwd";

	private static final String HOSTNAME = "monas_host";

	private static final String DB_NAME = "monas_dbname";

	private static final String DRIVER = "org.postgresql.Driver";

	private static MonasResourceDatabase resource = null;

	private Connection connection = null;

	private boolean admin = false;

	private MonasResourceDatabase() throws Exception {
		String userName = System.getProperty(USER);
		String password = System.getProperty(PASSWORD);
		String hostName = System.getProperty(HOSTNAME);
		String databaseName = System.getProperty(DB_NAME);

		Class.forName(DRIVER);

		String url = "jdbc:postgresql://" + hostName + "/" + databaseName;
		connection = DriverManager.getConnection(url, userName, password);
	}

	public static MonasResourceDatabase getInstance() throws Exception {
		if (resource == null) {
			resource = new MonasResourceDatabase();
		}
		return resource;
	}

	public boolean isAdmin() {
		return admin;
	}

	public void executeUpdate(String query) {
		Statement statement = null;

		try {
			statement = connection.createStatement();
			statement.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
	}

	public List<Object[]> executeQuery(String query) {
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		LinkedList<Object[]> list = new LinkedList<Object[]>();
		try {
			statement = connection.prepareStatement(query);
			resultSet = statement.executeQuery();

			ResultSetMetaData metaData = resultSet.getMetaData();

			while (resultSet.next()) {
				Object[] fields = new Object[metaData.getColumnCount()];
				for (int i = 0; i < fields.length; i++) {
					fields[i] = resultSet.getObject(i + 1);
				}
				list.addLast(fields);
			}
		} catch (Exception e) {
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
		return list;
	}

	/**
	 * Method untuk menghentikan koneksi database.
	 */
	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean login(String userName, String password) {
		String query = "SELECT * FROM tb_user WHERE name = '" + userName
				+ "' AND password = '" + password + "'";
		List<Object[]> list = executeQuery(query);
		if (list.size() > 0) {
			for (Object[] values : list) {
				admin = values[3].equals("Admin");
				return true;
			}
		}
		return false;
	}

	public List<Object[]> getLast(String id) {
		if (id.equals(MemoryView.ID)) {
			return getLastMemory();
		} else if (id.equals(HarddiskView.ID)) {
			return getLastHdd();
		} else if (id.equals(CPUView.ID)) {
			return getLastCPU();
		}
		return new LinkedList<Object[]>();
	}

	public List<Object[]> getLastCPU() {
		String query = getLastQuery("view_cpu");
		return executeQuery(query);
	}

	public List<Object[]> getLastHdd() {
		return executeQuery(getLastQuery("view_disk"));
	}

	public List<Object[]> getLastMemory() {
		return executeQuery(getLastQuery("view_memory"));
	}

	private String getLastQuery(String table) {
		return "SELECT cpu.* FROM "
				+ table
				+ " cpu JOIN (SELECT node, max(data_time) AS data_time FROM "
				+ table
				+ " GROUP BY node) last ON last.node = cpu.node AND last.data_time = cpu.data_time ORDER BY 1";
	}

	public List<Object[]> getHistory(String id, int limit) {
		if (id.equals(MemoryView.ID)) {
			return getMemoryHistory(limit);
		} else if (id.equals(HarddiskView.ID)) {
			return getHddHistory(limit);
		} else if (id.equals(CPUView.ID)) {
			return getCPUHistory(limit);
		}
		return new LinkedList<Object[]>();
	}

	public List<Object[]> getCPUHistory(int limit) {
		String query = "SELECT node, data_time, round((100::float - cpu_idle)::numeric, 1)::float AS "
				+ "cpu_used, cpu_idle FROM (SELECT ROW_NUMBER() OVER "
				+ "(PARTITION BY node order by data_time DESC) AS r, "
				+ "t.* FROM cpu t) x WHERE x.r <= " + limit;
		return executeQuery(query);
	}

	public List<Object[]> getHddHistory(int limit) {
		String query = "SELECT x.node, x.data_time, x.total, x.used, x.total - x.used AS avail "
				+ "FROM ( SELECT row_number() OVER (PARTITION BY t.node ORDER BY t.data_time DESC) "
				+ "AS r, t.node, t.data_time, t.total, t.used FROM ( SELECT df_h.node, df_h.data_time, "
				+ "sum(df_h.disk_size)::integer AS total, sum(df_h.disk_used)::integer AS used FROM df_h "
				+ "GROUP BY df_h.node, df_h.data_time) t) x WHERE x.r <= "
				+ limit;
		return executeQuery(query);
	}

	public List<Object[]> getMemoryHistory(int limit) {
		String query = "SELECT node, data_time, mem_total, mem_used, mem_free FROM (SELECT ROW_NUMBER() OVER "
				+ "(PARTITION BY node order by data_time DESC) AS r, "
				+ "t.* FROM free_m t) x WHERE x.r <= " + limit;
		return executeQuery(query);
	}

	public List<Object[]> getAllUser() {
		String query = "SELECT name, password, user_type, status, remark FROM tb_user ORDER BY 1";
		return executeQuery(query);
	}

	public List<Object[]> getNodes() {
		String query = "SELECT os.node, os.os, host.host, uname.username, pwd.password, endl.end_key, 'enable'::varchar AS status FROM "
				+ "(SELECT node, value AS os FROM tb_node WHERE key = 'operating system') os JOIN "
				+ "(SELECT node, value AS host FROM tb_node WHERE key = 'host') host ON host.node = os.node JOIN "
				+ "(SELECT node, value AS username FROM tb_node WHERE key = 'username') uname ON uname.node = os.node JOIN "
				+ "(SELECT node, value AS password FROM tb_node WHERE key = 'password') pwd ON pwd.node = os.node JOIN "
				+ "(SELECT node, value AS end_key FROM tb_node WHERE key = 'endline') endl ON endl.node = os.node ORDER BY 1";
		return executeQuery(query);
	}

	public void addUser(String username, String password, boolean isAdmin,
			String remark) {
		String type = "Admin";
		if (!isAdmin) {
			type = "Common";
		}
		String query = "INSERT INTO tb_user (name, password, user_type, "
				+ "status, remark) values ('" + username + "','" + password
				+ "','" + type + "','enable','" + remark + "')";
		executeUpdate(query);
	}

	public void editUser(String username, String password, String remark) {
		String query = "UPDATE tb_user SET password='" + password
				+ "', remark='" + remark + "' WHERE name='" + username + "'";
		executeUpdate(query);
	}

	public void deleteUser(String username) {
		String query = "DELETE FROM tb_user WHERE name='" + username + "'";
		executeUpdate(query);
	}

	public void deleteNode(String node) {
		String query = "DELETE FROM tb_node WHERE node='" + node + "'";
		executeUpdate(query);
	}

	public void addNode(Node node) {
		String query = "INSERT INTO tb_node VALUES ('" + node.getNode()
				+ "','host','" + node.getHost() + "');";
		query += "INSERT INTO tb_node VALUES ('" + node.getNode()
				+ "','operating system','" + node.getOs() + "');";
		query += "INSERT INTO tb_node VALUES ('" + node.getNode()
				+ "','username','" + node.getUsername() + "');";
		query += "INSERT INTO tb_node VALUES ('" + node.getNode()
				+ "','password','" + node.getPassword() + "');";
		query += "INSERT INTO tb_node VALUES ('" + node.getNode()
				+ "','endline','" + node.getEndLine() + "');";
		executeUpdate(query);
	}

	public void editNode(Node node) {
		String query = "UPDATE tb_node SET value='" + node.getHost()
				+ "' WHERE key='host' and node='" + node.getNode() + "';";
		query += "UPDATE tb_node SET value='" + node.getOs()
				+ "' WHERE key='operating system' and node='" + node.getNode()
				+ "';";
		query += "UPDATE tb_node SET value='" + node.getUsername()
				+ "' WHERE key='username' and node='" + node.getNode() + "';";
		query += "UPDATE tb_node SET value='" + node.getPassword()
				+ "' WHERE key='password' and node='" + node.getNode() + "';";
		query += "UPDATE tb_node SET value='" + node.getEndLine()
				+ "' WHERE key='endline' and node='" + node.getNode() + "';";
		executeUpdate(query);
	}

	public List<Object[]> getDashboardCPU() {
		return executeQuery("SELECT node, data_time, round(cpu_used::numeric, 2) "
				+ "AS used FROM view_cpu");
	}

	public List<Object[]> getDashboardMemory() {
		return executeQuery("SELECT node, data_time, round(((mem_used::decimal / "
				+ "mem_total::decimal) * 100)::numeric, 2) AS used FROM view_memory");
	}

	public List<Object[]> getDashboardHdd() {
		return executeQuery("SELECT node, data_time, round(((used::decimal / "
				+ "total::decimal) * 100)::numeric, 2) AS used FROM view_disk");
	}

}
